package com.egao.common.module.system.transfer;

import com.egao.common.core.web.JsonResult;
import com.egao.common.module.company.constants.CheckRecordConstants;
import com.egao.common.module.company.entity.Attachment;
import com.egao.common.module.company.entity.Company;
import com.egao.common.module.company.entity.Preach;
import com.egao.common.module.company.service.AttachmentService;
import com.egao.common.module.company.service.CompanyService;
import com.egao.common.module.company.service.PositionService;
import com.egao.common.module.company.service.PreachService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.sql.*;
import java.util.List;

/**
 * 空中宣讲会数据导入
 * @author: dl
 */
@RestController
@Slf4j
public class PreachFileDataController {

    @Value("${old.datasource.jyurl}")
    private String url;

    @Value("${old.datasource.username}")
    private String userName;

    @Value("${old.datasource.password}")
    private String password;

    @Value("${minio.endpoint}")
    private String minioUrl;

    @Autowired
    private PreachService preachService;

    @Autowired
    private AttachmentService attachmentService;

    @Transactional(rollbackFor = Exception.class)
    public JsonResult transfer() {
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        long beginTime = System.currentTimeMillis();
        List<Preach> preachList = getPreachList();
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = DriverManager.getConnection(url, userName, password);
            st = conn.createStatement();
            String sql = "SELECT " +
                    " ID, " +
                    " PreachID, " +
                    " FileName, " +
                    " FileUrl, " +
                    " create_by, " +
                    " create_date, " +
                    " update_by, " +
                    " update_date, " +
                    " remarks, " +
                    " del_flag " +
                    " FROM " +
                    // 过滤没有宣讲会ID附件
                    " preachfile WHERE PreachID is not null";
            st = conn.createStatement();
            rs = st.executeQuery(sql);
            log.info("总数：{}", rs.getRow());
            int i = 0;
            while (rs.next()) {
                String oldId = rs.getString("id");
                String PreachID = rs.getString("PreachID");
                log.info("PreachID  : {}", PreachID);
                String FileName = rs.getString("FileName");
                String FileUrl = rs.getString("FileUrl");
                String del_flag = rs.getString("del_flag");

                Date createDate = new Date(rs.getTimestamp("create_date").getTime());
                Attachment attachment = new Attachment();
                if (rs.getTimestamp("update_date") != null) {
                    Date updateDate = new Date(rs.getTimestamp("update_date").getTime());
                    attachment.setUpdateTime(updateDate);
                }

                attachment.setOldId(oldId);
                attachment.setName(FileName);
                attachment.setType(Integer.parseInt(CheckRecordConstants.CHECK_PREACH));
                attachment.setBusinessId(getPreachId(preachList, PreachID));
                attachment.setUrl(minioUrl + FileUrl.replaceAll("/upload/files", "/zuel/old"));

                attachment.setCreateTime(createDate);
                attachment.setDeleted("0".equals(del_flag) ? 0 : 1);

                i ++;
                log.info("宣讲会附件：{} 第：{} 个", FileName, i);
                if (attachmentService.save(attachment)) {
                    log.info("宣讲会附件：{} 第：{} 个", FileName, i);
                }
            }
            long overTime = System.currentTimeMillis();
            long finishTime = (overTime - beginTime) / 1000;
            log.info("完成时间 : {} 分", finishTime);
            return JsonResult.ok(String.format("完成时间 : %s 分", finishTime));
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } finally {
            try {
                rs.close();
                st.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return JsonResult.ok();
    }
    
    /** 
     * 查询所有宣讲会信息
     * @return 
     */
    private List<Preach> getPreachList() {
        return preachService.list();
    }

    /**
     * 查询原始
     * @return
     */
    private Integer getPreachId(List<Preach> preachList, String oldId) {
        for(Preach preach : preachList) {
            if (oldId.equals(preach.getOldId())) {
                return preach.getId();
            }
        }
        return 0;
    }

}
